﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Net;
using System.IO;
using System.Data.Objects;

namespace ImportSentinal
{
    class Program
    {
        static void Main(string[] args)
        {
            SalesManagerEntities sme = new SalesManagerEntities();

            tblImportSentinal[] watches = (from c in sme.tblImportSentinal select c).ToArray();
            foreach (tblImportSentinal watch in watches)
            {
                string[] files = Directory.GetFiles(watch.WatchPath, watch.FileMask);

                foreach (string file in files)
                {
                    // Attempt to open the file exclusively. If this is succesfull then the file is complete 
                    // and ready to be imported.
                    FileStream fs;
                    bool opened = false;
                    try
                    {
                        fs = System.IO.File.Open(file, FileMode.Open, FileAccess.Read, FileShare.None);
                        fs.Close();
                        fs.Dispose();
                        opened = true;
                    }
                    catch (Exception)
                    {
                    }

                    if (opened)
                    {
                        string emailSubject = string.Empty;
                        string emailBody = string.Empty;

                        SqlConnection conn = (SqlConnection)((System.Data.EntityClient.EntityConnection)sme.Connection).StoreConnection;
                        if (conn.State != System.Data.ConnectionState.Open)
                            conn.Open();
                        SqlCommand cmm = conn.CreateCommand();
                        cmm.CommandType = System.Data.CommandType.StoredProcedure;

                        // Run the import procedure
                        cmm.CommandText = watch.ImportSpec.StoredProcedure;
                        cmm.CommandTimeout = 600; // Set the timeout to 10 minutes
                        cmm.Parameters.Clear();
                        cmm.Parameters.Add(new SqlParameter("@ReturnValue", System.Data.SqlDbType.Int)).Direction = System.Data.ParameterDirection.ReturnValue;
                        cmm.Parameters.Add(new SqlParameter("@OwnerID", watch.OwnerID));
                        cmm.Parameters.Add(new SqlParameter("@FileName", file));
                        cmm.Parameters.Add(new SqlParameter("@TemplateFile", (watch.ImportSpec.TemplateFile == null) ? DBNull.Value : (object)watch.ImportSpec.TemplateFile));
                        cmm.Parameters.Add(new SqlParameter("@TransformProcedure", (watch.ImportSpec.TransformProcedure == null) ? DBNull.Value : (object)watch.ImportSpec.TransformProcedure));
                        SqlDataReader rdr = null;
                        try
                        {
                            Int32 importBatchID = 0;

                            rdr = cmm.ExecuteReader();

                            ObjectResult<spImportOrder_Result> results =
                                sme.Translate<spImportOrder_Result>(rdr);
                            rdr.Close();
                            if (cmm.Parameters["@ReturnValue"].Value != null)
                                importBatchID = (Int32)cmm.Parameters["@ReturnValue"].Value;

                            if (importBatchID != 0)
                            {
                                tblImportBatch importBatch = (tblImportBatch)
                                    sme.GetObjectByKey(new System.Data.EntityKey("SalesManagerEntities.tblImportBatch", "ImportBatchID", importBatchID));

                                emailSubject = importBatch.Imported.ToString() +
                                    " imported for " + watch.Owner.ShortName +
                                    " from file " + Path.GetFileName(file);
                                emailBody = "Import batch ID: " + importBatchID.ToString() +
                                    "\nLines read: " + importBatch.Lines +
                                    "\nOrders loaded: " + importBatch.Imported +
                                    "\n\n";

                                foreach (spImportOrder_Result result in results)
                                    emailBody += result.LineNumber.ToString() + "\t" + result.Message + "\n";
                            }
                            else
                            {
                                emailSubject = "Nothing imported for " + watch.Owner.ShortName +
                                    " from file " + Path.GetFileName(file);
                            }
                        }
                        catch (Exception err)
                        {
                            emailSubject = "Error importing file " + Path.GetFileName(file) + " for " + watch.Owner.ShortName;
                            emailBody = err.Message;
                            if (rdr != null)
                                if (!rdr.IsClosed)
                                    rdr.Close();
                            return;
                        }

                        // Send out the results in an email.
                        if (watch.EmailTo != null)
                        {
                            string mailServer = System.Configuration.ConfigurationManager.AppSettings["MailServer"];
                            if (mailServer == null)
                                throw new Exception("MailServer is not set in the confirutaion");
                            System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient(mailServer);
                            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();

                            message.Subject = emailSubject;
                            foreach (string to in watch.EmailTo.Split(new char[] { ';' }))
                                if (to.Trim() != string.Empty)
                                    message.To.Add(to);

                            message.Body = emailBody;
                            message.From = new System.Net.Mail.MailAddress(System.Configuration.ConfigurationManager.AppSettings["MailFrom"]);

                            if (message.To.Count > 0)
                            {
                                client.Credentials = new NetworkCredential(System.Configuration.ConfigurationManager.AppSettings["MailUID"],
                                    System.Configuration.ConfigurationManager.AppSettings["MailPWD"]);
                                client.Send(message);
                            }
                        }

                        // Move the file to the archive patch
                        watch.ArchivePath = watch.ArchivePath.Trim();
                        if (!watch.ArchivePath.EndsWith("\\"))
                            watch.ArchivePath = watch.ArchivePath + "\\";
                        if (File.Exists(watch.ArchivePath + Path.GetFileName(file)))
                        {
                            int index = 0;
                            while (File.Exists(watch.ArchivePath + Path.GetFileName(file) + "_" + index.ToString()))
                                index++;
                            File.Move(file,
                                watch.ArchivePath + Path.GetFileName(file) + "_" + index.ToString());
                        }
                        else
                            File.Move(file, 
                                watch.ArchivePath + Path.GetFileName(file));
                    }
                }
            }
        }
    }
}
